package com.cf.studentsociety.dao.impl;

import com.cf.studentsociety.dao.BaseDao;
import com.cf.studentsociety.dao.SocietyDao;
import com.cf.studentsociety.entity.Society;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class SocietyDaoImpl extends BaseDao implements SocietyDao {
    @Override
    public Integer insertSociety(Society society) throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());

//        // 1. 查询是否已存在相同的 society_creator 值
//        String checkSql = "SELECT COUNT(*) FROM society WHERE society_creator = ?";
//        Long count = qr.query(checkSql, new ScalarHandler<>(), society.getSociety_creator());
//
//        // 2. 如果存在重复的值，抛出异常
//        if (count > 0) {
//            throw new SQLException("Duplicate society_creator: " + society.getSociety_creator());
//        }
//
//        // 3. 如果没有重复，执行插入操作
        String sql = "INSERT INTO society (society_creator, society_create_date, society_name, society_intro, society_status) VALUES (?, ?, ?, ?, ?)";
        return qr.update(sql, society.getSociety_creator(), society.getSociety_create_date(),
                society.getSociety_name(), society.getSociety_intro(), society.getSociety_status());
    }

    @Override
    public List<Society> getAllSociety() throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "select * from society";
        return qr.query(sql,new BeanListHandler<Society>(Society.class));
    }
    @Override
    public Integer updateSociety(Society society) throws SQLException {
        StringBuffer buffer = new StringBuffer("update society set  ");
        QueryRunner qr = new QueryRunner(getDataSource());
        int result = 0;
        if(society.getSociety_name() != null){
            buffer.append(" society_name = ? ").append("where societyId = ?");
            result = qr.update(buffer.toString(),society.getSociety_name(),society.getSocietyId());
        }else if (society.getSociety_intro() != null){
            buffer.append(" society_intro = ? ").append("where societyId = ?");
            result = qr.update(buffer.toString(),society.getSociety_intro(),society.getSocietyId());
        }else if (society.getSociety_status() != null){
            buffer.append(" society_status = ? ").append("where societyId = ?");
            result = qr.update(buffer.toString(),society.getSociety_status(),society.getSocietyId());
        }
        return result;
    }
    @Override
    public Society queryByStudentNumber(String number) throws SQLException {
        String sql = "select a.* from society a,student,member where studentNumber = ? and student.studentId = member.memberStudentId and member.member_societyId = a.societyId";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql,new BeanHandler<Society>(Society.class),number);
    }

    @Override
    public Society queryByStudentNumbers(String number) throws SQLException {
        String sql = "SELECT s.societyId, s.society_name, s.society_intro, s.society_status, s.society_creator, s.society_create_date\n" +
                "FROM society s\n" +
                "INNER JOIN student st ON s.society_creator = st.studentId\n" +
                "WHERE st.studentNumber = ?\n";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql,new BeanHandler<Society>(Society.class),number);
    }


}
